package com.kaikeba.dao.impl;

import com.kaikeba.bean.Courier;
import com.kaikeba.dao.BaseCourierDao;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: 平川
 * @Description:
 * @Date Created in 2021-08-02 9:48
 * @Modified By:
 */
public class CourierDaoMysql implements BaseCourierDao {
    public static final String SQL_CONSOLE="SELECT COUNT(*) data_size,COUNT(TO_DAYS(intime)=TO_DAYS(NOW())OR NULL ) data_day FROM courier";
    //用于查询所有的数据库信息
    public static final String SQL_FIND_ALL="SELECT * FROM courier";
    //用于分页查询所有的数据库信息
    public static final String SQL_FIND_LIMIT="SELECT * FROM courier LIMIT ?,?";
    //通过id查询
    public static final String SQL_FIND_BY_ID="SELECT*FROM COURIER WHERE ID =?";
    //通过手机号码查询
    public static final String SQL_FIND_BY_phonenumber="SELECT*FROM COURIER WHERE phonenumber =?";
    //通过cardid查询
    public static final String SQL_FIND_BY_CARDID="SELECT*FROM COURIER WHERE CARDID =?";
    //插入
    public static final String SQL_INERT="INSERT INTO COURIER(USERNAME,PHONENUMBER,CARDID,PASSWORD,SENDNUMBER,INTIME,OUTTIME) VALUES(?,?,?,?,?,NOW(),now())";
    //更新
    public static final String SQL_UPDATE="UPDATE courier SET username=?,phonenumber=?,cardid=?,PASSWORD=? WHERE ID=?" ;
    //根据id 去修改用户名，与用户手机号的信息
    public static final String SQL_UPDATE_Username_userphone="UPDATE courier SET username=?,phonenumber=? WHERE ID=?" ;
    //根据用户名 去修改用户名，与用户手机号 ，个人密码
    public static final String SQL_UPDATE_Username_userphone_PASSWORD="UPDATE courier SET username=?,phonenumber=?,password=? WHERE ID=?" ;


    //根据手机号更新最近登陆时间
    public static final String SQL_UPDATETIME="UPDATE courier SET OUTTIME=NOW() WHERE phonenumber=? ";

    //删除
    public static final String SQL_DELETE="DELETE FROM  COURIER WHERE id=?";



    /**
     * @return 快递员的人数
     */
    @Override
    public List<Map<String, Integer>> console() {
        List<Map<String,Integer>> list = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        ResultSet res = null;
        try {
            pre = conn.prepareStatement(SQL_CONSOLE);
            res = pre.executeQuery();
            while (res.next()){
                int data_size = res.getInt("data_size");
                int data_day = res.getInt("data_day");
                Map<String, Integer> map = new HashMap<>();
                map.put("data_size",data_size);
                map.put("data_day",data_day);
                list.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,res);
        }
        return list;
    }

    /**
     * @param limit
     * @param offset
     * @param pageNumber
     * @return 快递员的集合
     */
    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        List<Courier> list = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        ResultSet res = null;
        try{
            if (limit ){
               pre = conn.prepareStatement(SQL_FIND_LIMIT);
               pre.setObject(1,offset);
               pre.setObject(2,pageNumber);
            }else{
               pre = conn.prepareStatement(SQL_FIND_ALL);
            }
            res = pre.executeQuery();
            while (res.next()){
                int id = res.getInt("id");
                String username = res.getString("username");
                String phonenumber = res.getString("phonenumber");
                String cardid = res.getString("cardid");
                String password = res.getString("password");
                int sendnumber = res.getInt("sendnumber");
                Timestamp intime = res.getTimestamp("intime");
                Timestamp outtime = res.getTimestamp("outtime");
                Courier courier = new Courier(id,username,phonenumber,cardid,password,sendnumber,intime,outtime);
                list.add(courier);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,res);
        }
        return list;
    }

    /**
     * 根据快递员的id去查
     *
     * @param id
     * @return 返回快递员的信息
     */
    @Override
    public Courier findByID(int id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        ResultSet res = null;
        try {
            pre = conn.prepareStatement(SQL_FIND_BY_ID);
            pre.setObject(1,id);
            res = pre.executeQuery();
            if (res.next()){
                String username = res.getString("username");
                String phonenumber = res.getString("phonenumber");
                String cardid = res.getString("cardid");
                String password = res.getString("password");
                int sendnumber = res.getInt("sendnumber");
                Timestamp intime = res.getTimestamp("intime");
                Timestamp outtime = res.getTimestamp("outtime");
                Courier courier = new Courier(id,username,phonenumber,cardid,password,sendnumber,intime,outtime);
                return courier;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,res);
        }
        return null;
    }

    /**
     * 根据手机号码查询
     *
     * @param phonenumber
     * @return 快递员
     */
    @Override
    public Courier findByphonenumber(String phonenumber) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        ResultSet res = null;
        try {
            pre = conn.prepareStatement(SQL_FIND_BY_phonenumber);
            pre.setObject(1,phonenumber);
            res = pre.executeQuery();
            if (res.next()){
                int id = res.getInt("id");
                String username = res.getString("username");
                String cardid = res.getString("cardid");
                String password = res.getString("password");
                int sendnumber = res.getInt("sendnumber");
                Timestamp intime = res.getTimestamp("intime");
                Timestamp outtime = res.getTimestamp("outtime");
                Courier courier = new Courier(id,username,phonenumber,cardid,password,sendnumber,intime,outtime);
                return courier;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,res);
        }
        return null;
    }

    /**
     * 根据身份证号码去查
     *
     * @param cardid
     * @return 返回快递员的信息
     */
    @Override
    public Courier findByCardid(String cardid) {
            Connection conn = DruidUtil.getConnection();
            PreparedStatement pre = null;
            ResultSet res = null;
            try {
                pre = conn.prepareStatement(SQL_FIND_BY_CARDID);
                pre.setObject(1,cardid);
                res = pre.executeQuery();
                if (res.next()){
                    int id = res.getInt("id");
                    String username = res.getString("username");
                    String phonenumber = res.getString("phonenumber");
                    String password = res.getString("password");
                    int sendnumber = res.getInt("sendnumber");
                    Timestamp intime = res.getTimestamp("intime");
                    Timestamp outtime = res.getTimestamp("outtime");
                    Courier courier = new Courier(id,username,phonenumber,cardid,password,sendnumber,intime,outtime);
                    return courier;
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                DruidUtil.close(conn,pre,res);
            }
        return null;
    }

    /**
     * @param courier
     * @return 新增快递员
     */
    @Override
    public boolean insert(Courier courier) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        try {
            pre = conn.prepareStatement(SQL_INERT);
            pre.setObject(1,courier.getUsername());
            pre.setObject(2,courier.getPhonenumber());
            pre.setObject(3,courier.getCardid());
            pre.setObject(4,courier.getPassword());
            pre.setObject(5,courier.getSendnumber());
            return pre.executeUpdate()==0?false:true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,null);
        }
        return false;
    }

    /**
     * 根据id号去修改快递员的信息
     *
     * @param id
     * @param courier
     * @return 修改快递员的信息
     */
    @Override
    public boolean update(int id, Courier courier) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        try {
            pre = conn.prepareStatement(SQL_UPDATE);
            pre.setObject(1,courier.getUsername());
            pre.setObject(2,courier.getPhonenumber());
            pre.setObject(3,courier.getCardid());
            pre.setObject(4,courier.getPassword());
            pre.setObject(5,id);

            return pre.executeUpdate()==0?false:true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,null);
        }
        return false;
    }

    /**
     * 根据id号去修改快递员的信息
     *
     * @param id
     * @param username
     * @param userphone
     * @return 修改快递员的信息
     */
    @Override
    public boolean updateUsernameUserphone(int id, String username, String userphone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        try {
            pre = conn.prepareStatement(SQL_UPDATE_Username_userphone);
            pre.setObject(1,username);
            pre.setObject(2,userphone);
            pre.setObject(3,id);
            return pre.executeUpdate()==0?false:true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,null);
        }
        return false;
    }

    /**
     * 根据id号去修改快递员的信息
     *
     * @param id
     * @param username
     * @param userphone
     * @param password
     * @return 修改快递员的信息
     */
    @Override
    public boolean updateUsernameUserphonePassword(int id, String username, String userphone, String password) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        try {
            pre = conn.prepareStatement(SQL_UPDATE_Username_userphone_PASSWORD);
            pre.setObject(1,username);
            pre.setObject(2,userphone);
            pre.setObject(3,password);
            pre.setObject(4,id);
            return pre.executeUpdate()==0?false:true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,null);
        }
        return false;
    }

    /**
     * 根据手机号，更新用户最近的登陆时间
     *
     * @param userphone
     */
    @Override
    public boolean updatetime(String userphone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        try {
            pre = conn.prepareStatement(SQL_UPDATETIME);
            pre.setObject(1,userphone);
            return pre.executeUpdate()==0?false:true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,null);
        }
        return false;
    }

    /**
     * 根据id去删除快递员的信息
     *
     * @param id
     * @return 删除true false
     */
    @Override
    public boolean delete(int id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        try {
            pre = conn.prepareStatement(SQL_DELETE);
            pre.setObject(1,id);
            return pre.executeUpdate()==0?false:true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,null);
        }
        return false;
    }
}
